package lib.persistence.commands;

import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import lib.persistence.NotFoundException;
import models.Person;

public class GetRow extends CommandAbstract
{

	private Connection conn;
	public GetRow(Connection connection) 
	{
		this.conn = connection;
	}
	public GetRow()
	{
		this.conn = global.ConnectionManager.getInstance().getConnection();
	}
	public Object findObjectById(int rowId, String tableName, String columnName) throws NotFoundException, SQLException, IOException, ClassNotFoundException 
	{
		
		String cmd = String.format("SELECT %s FROM %s WHERE id = %d",columnName, tableName, rowId);
		//System.out.println(cmd);
		PreparedStatement statement = conn.prepareStatement(cmd);
		ResultSet rs = statement.executeQuery();
		if (rs.next())
		{
			   InputStream is = rs.getBlob(columnName).getBinaryStream();
			    ObjectInputStream oip = new ObjectInputStream(is);
			    return oip.readObject();
		}
		throw new NotFoundException("id not found!");
	}
	public int findIntById(int rowId, String tableName , String columnName) throws NotFoundException, SQLException
	{
		String cmd = String.format("SELECT %s FROM %s WHERE id = %d",columnName, tableName, rowId);
		PreparedStatement statement = conn.prepareStatement(cmd);
		ResultSet rs = statement.executeQuery();
		if (rs.next())
		{
			return rs.getInt(columnName);
		}
		throw new NotFoundException("id not found!");
	}
	public List<Integer> findIntByColumnWhereIsInt(String whereColumn,int whereVal,String tableName, String columnName) throws SQLException
	{
		String cmd = String.format("SELECT %s FROM %s WHERE %s = %d",columnName, tableName,whereColumn, whereVal);
		PreparedStatement statement = conn.prepareStatement(cmd);
		ResultSet rs = statement.executeQuery();
		List<Integer> out = new ArrayList<Integer>();
		while (rs.next())
		{
			out.add(rs.getInt(columnName));
		}
		
		return out;
	}
	public ArrayList<Object> findObjects(String tableName, String columnName) throws SQLException, IOException, ClassNotFoundException 
	{
		String cmd = String.format("SELECT %s FROM %s ",columnName, tableName);
		System.out.println(cmd);
		PreparedStatement statement = conn.prepareStatement(cmd);
		ResultSet rs = statement.executeQuery();
		ArrayList<Object> out = new ArrayList<Object>();
		while(rs.next())
		{
			InputStream is = rs.getBlob(columnName).getBinaryStream();
			ObjectInputStream oip = new ObjectInputStream(is);
			out.add(oip.readObject());
		}
		return out; 	
	}
	public ArrayList<Object> findObjectsByLatest(int limit,  String orderBy, String tableName, String columnName)  throws SQLException, IOException, ClassNotFoundException 
	{

		String cmd = String.format("SELECT id, %s FROM %s ORDER BY %s LIMIT 0, %d",columnName, tableName, orderBy , limit);
		System.out.println(cmd);
		PreparedStatement statement = conn.prepareStatement(cmd);
		ResultSet rs = statement.executeQuery();
		ArrayList<Object> out = new ArrayList<Object>();
		while(rs.next())
		{
			InputStream is = rs.getBlob(columnName).getBinaryStream();
			ObjectInputStream oip = new ObjectInputStream(is);
			out.add(oip.readObject());
		}
		return out; 	
	}
	//for this function the dates should be of the format 'yyyy-mm-dd hh:mm:ss'
	public ArrayList<Object> findObjectsByDateRange(int limit,  String DateHigh, String DateLow, String tableName, String columnName) throws SQLException, IOException, ClassNotFoundException 
	{
		String cmd = String.format("SELECT id, createdAt, %s FROM %s WHERE createdAt > '%s' AND createdAt < '%s' ORDER BY createdAt LIMIT 0, %d",columnName, tableName, DateLow, DateHigh, limit);
		System.out.println(cmd);
		PreparedStatement statement = conn.prepareStatement(cmd);
		ResultSet rs = statement.executeQuery();
		ArrayList<Object> out = new ArrayList<Object>();

		while(rs.next())
		{
			InputStream is = rs.getBlob(columnName).getBinaryStream();
			ObjectInputStream oip = new ObjectInputStream(is);
			out.add(oip.readObject());
		}
		return out; 	
	}
	public ArrayList<Object> findObjectByWhere(String tableName, String columnName, String WhereColumnName, String WhereValue) throws SQLException, IOException, ClassNotFoundException 
	{
		String cmd = String.format("SELECT %s FROM %s WHERE %s = '%s'",columnName, tableName, WhereColumnName, WhereValue);
		System.out.println(cmd);
		PreparedStatement statement = conn.prepareStatement(cmd);
		ResultSet rs = statement.executeQuery();
		ArrayList<Object> out = new ArrayList<Object>();
		while(rs.next())
		{
			InputStream is = rs.getBlob(columnName).getBinaryStream();
			ObjectInputStream oip = new ObjectInputStream(is);
			out.add(oip.readObject());
		}
		return out; 	
	}
}
